package com.luinnx.message.DB;

import com.luinnx.message.model.Leavemessage;
import com.luinnx.message.model.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class DB {
	private String url="jdbc:mysql://localhost:3306/message";
	private String user="root";
	private String password="root";
	private Connection ct;
	private PreparedStatement psmt;
	
	public DB(){
		
		try {
			Class.forName("com.mysql.jdbc.Driver");//注册mysql数据库
			ct=DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	//登陆密码校验
	public User checkUser(String username,String password){
		String sql="select * from user where username=? and password=?";
		ResultSet rs=null;
		try {
			psmt=ct.prepareStatement(sql);
			psmt.setString(1, username);
			psmt.setString(2, password);
			rs=psmt.executeQuery();
			
			User user=new User();
			while(rs.next()){
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				return user;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	//查找所有帖子
	public ArrayList<Leavemessage> findAll() {
		// TODO Auto-generated method stub
		String sql="select * from Leavemessage ";
		ResultSet rs=null;
		ArrayList<Leavemessage> al=new ArrayList<Leavemessage>();
		try{
			psmt=ct.prepareStatement(sql);
			rs=psmt.executeQuery();
			while(rs.next()){
				Leavemessage ly=new Leavemessage();
				ly.setId(rs.getInt(1));
				ly.setUserId(rs.getInt(2));
				ly.setDate(rs.getDate(3));
				ly.setTitle(rs.getString("title"));
				ly.setType(rs.getString("type"));
				ly.setContent(rs.getString("content"));
				al.add(ly);
				System.out.println(ly.getDate());
			}
			
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return al;
	}
	
	//获取用户名字
	public String getUserName(int userid){
		String username=null;
		String sql="select username from user where id=?";
		try {
			psmt=ct.prepareStatement(sql);
			psmt.setInt(1, userid);
			ResultSet rs=psmt.executeQuery();
			while(rs.next()){
				username=rs.getString("username");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("useranem:  "+username);
		return username;
	}

	//获取用户id
	public int getUserId(String username) {
		// TODO Auto-generated method stub
		int userId=0;
		String sql="select * from User where username = ?";
		PreparedStatement psmt;
		try {
			psmt = ct.prepareStatement(sql);
			psmt.setString(1, username);
			ResultSet rs=psmt.executeQuery();
			while(rs.next()){
				userId=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return userId;
		
	}
	
	//新增留言
	public boolean addMes(Leavemessage ly) {
		// TODO Auto-generated method stub
		String sql="insert into Leavemessage(userid,date,title,type,content) values(?,?,?,?,?)";
		try {
			PreparedStatement psmt=ct.prepareStatement(sql);
			psmt.setInt(1, ly.getUserId());
			psmt.setDate(2, ly.getDate());
			psmt.setString(3, ly.getTitle());
			psmt.setString(4, ly.getType());
			psmt.setString(5, ly.getContent());
			psmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
	}
	
	//新增用户
	public boolean insertUser(User user) {
		// TODO Auto-generated method stub
		String sql="insert into user(username,password,age,cellphone,email) values(?,?,?,?,?)";
		
		try {
			PreparedStatement psmt=ct.prepareStatement(sql);
			psmt.setString(1, user.getUsername());
			psmt.setString(2, user.getPassword());
			psmt.setString(3, user.getAge());
			psmt.setString(4, user.getCellphone());
			psmt.setString(5, user.getEmail());
			psmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
		
	}

}
